<?php

namespace ulue\core\db\base\query;

abstract class AbstractNodeBuilder extends AbstractQuery
{

    public $select          = '*';
    public $from            = '';
    public $join            = ''; // inner join | outer join | left Join | right Join

    public $where           = '';

    public $in              = '';
    public $like            = '';
    public $between         = '';

    public $order           = '';
    public $group           = '';
    public $having          = '';

    public $limit           = '';
    public $offset          = '';

    public $union           = '';

    // public $condition       = ''; // array | string
    public $params          = []; // array | string

////////////////////////////////////////// build sql element function //////////////////////////////////////////


    /**
     * select 查询字段
     * @param string $fields
     * @return $this
     */
    public function select($fields='*')
    {
        $this->select = 'SELECT ' . $this->handleColumn($fields);

        unset($fields);

        return $this;
    }

    /**
     * [exclude 查询字段较多，但不想查询全部,想排除一些不需要的字段，与select()相反 ]
     * @param string $fields
     * @param null $table
     * @return $this
     */
    public function exclude($fields, $table=null)
    {
        if (!$fields) {
            return $this->select();
        }

        if (is_string($fields)) {
            $fields = strpos($fields, ',')===false ? [$fields] : explode(',', $fields);
        }

        $table    = $table ?: $this->table;
        $fieldArr = $this->db->getTable($table)->getColumnNames(true);

        foreach ($fieldArr as $key => $field) {
            if (in_array($field, $fields)) {
                unset($fieldArr[$key]);
            }
        }

        $this->select = 'SELECT ' . $this->handleColumn($fieldArr);

        unset($fields);

        return $this;
    }

    /**
     * @param string $table
     * @return $this
     */
    public function from($table='')
    {
        $table    = $table ?: $this->table;

        #参数不为空时
        $this->from = 'FROM ' . $this->quoteName($table);

        return $this;
    }

    /**
     * sql join 语句 e.g. 'table1 left join table2 on table1.id = table2.uid'
     * @param $table
     * @param $on
     * @param  string $type
     * @return $this
     */
    public function join($table, $on, $type='INNER')
    {
        $this->join = "$type JOIN " . $this->quoteName($table) . ' ON ' . $on;

        return $this;
    }

    /**
     * @param $table
     * @param $on
     * @return AbstractQuery
     */
    public function leftJoin($table, $on)
    {
        return $this->join($table, $on, 'LEFT');
    }

    /**
     * @param $table
     * @param $on
     * @return AbstractQuery
     */
    public function rightJoin($table, $on)
    {
        return $this->join($table, $on, 'RIGHT');
    }

    /**
     * @param $table
     * @param $on
     * @return AbstractQuery
     */
    public function on($where)
    {
        $this->on = 'ON ' .trim($where);

        return $this;
    }

    public function joinOn($table, $on, $type='INNER')
    {
        $this->join = "$type JOIN " . $this->quoteName($table) . ' ON ' . $on;

        return $this;
    }

    /**
     * @param $condition
     * 1 id = 5 AND name = 'hello'
     * 2 array('id'=>5 , 'name' => 'hello' )
     * 3 object(stdClass)#4 (1) {["id"]=>int(5)}
     * ... more @see $this->handleWhere()
     * @param array $param
     * @param string $separator
     * @return $this
     */
    public function where($condition=null, array $param=[])
    {
        if ($condition) {
            $this->where  = 'WHERE ' . $this->handleCondition($condition);

            $this->addParams($param);
        }

        return $this;
    }

    /**
     * @param $column
     * @param $range
     * @return $this
     */
    public function in($column, $range)
    {
        if (is_array($range)) {
            $range = implode(',', $range);
        }

        if ($this->where) {
            $this->in = "AND $column IN ( $range )";
        } else {
            $this->in = "WHERE $column IN ( $range )";
        }

        return $this;
    }

    /**
     * @param $column
     * @param $range
     * @return $this
     */
    public function notIn($column, $range)
    {
        if (is_array($range)) {
            $range = implode(',', $range);
        }

        $this->in = "$column NOT IN ( $range )";

        return $this;
    }

    /**
     * @param $column
     * @param $keywords
     * @return $this
     */
    public function like($column, $keywords)
    {
        $keywords   = trim($keywords,'% ');
        $this->like = "$column LIKE %{$keywords}%";

        return $this;
    }

    /**
     * @param $column
     * @param $start
     * @param $end
     * @return $this
     */
    public function between($column, $start, $end)
    {
        $this->between = "$column BETWEEN $start AND $end";

        return $this;
    }

    /**
     * @param $column
     * @param $start
     * @param $end
     * @return $this
     */
    public function notBetween($column, $start, $end)
    {
        $this->between = "$column NOT BETWEEN $start AND $end ";

        return $this;
    }

    /**
     * @param $condition
     * 1 array('id asc','date desc')--> order by id asc,date desc
     * 2 id asc--> order by id asc
     * 3 id --> order by id asc
     * @param  integer $type
     * @return $this
     */
    public function order($condition, $type='ASC')
    {
        $type = trim($type);

        if (is_array($condition)) {
            $condition  = implode(' , ', $condition);
        } else if ( strpos(trim($condition),' ')>0 ) {
            $condition  = str_ireplace([' asc',' desc'], [' ASC',' DESC'], $condition);
        } else {
            $condition  = $condition . " $type";
        }

        $this->order  = "ORDER BY {$condition}";

        return $this;
    }

    /**
     * @param $condition
     * @return $this
     */
    public function group($condition)
    {
        $this->group = "GROUP BY {$condition}";

        return $this;
    }

    /**
     * @param $condition
     * @return $this
     */
    public function having($condition)
    {
        $this->having = "HAVING $condition";

        return $this;
    }

    /**
     * @param int $limit
     * @param int $offset
     * @return $this
     */
    public function limit( $limit=1, $offset=0 )
    {
        if ( is_numeric($offset) && is_numeric($limit) ) {
            (int)$offset< 0  && $offset   = 0;
            (int)$limit< 1 && $limit = 1;

            $this->limit = 'LIMIT ' . trim($limit);
            $this->offset($offset);
        } else {
            \Trigger::error('传入参数(limit: '.$limit.',offset: '.$offset.')类型错误！LIMIT 只允许不小于零的自然数');
        }

        return $this;
    }

    /**
     * @param $int
     * @return $this
     */
    public function offset($int)
    {
        $int =  (int)$int< 0 ? 0 :(int)$int;

        $this->offset  = 'OFFSET ' . $int;

        return $this;
    }

    /**
     * select1 union select2 联合查询有重复去掉保留一行
     * select2 union all select2 所有行合并到结果集中去
     * @param $sql
     * @param string $type e.g. 'ALL'
     * @return $this
     */
    public function union($sql, $type='')
    {
        $type = strtoupper(trim($type));

        $this->union = "UNION $type ". (string)$sql;

        return $this;
    }

    /**
     * @param $sql
     * @return $this
     */
    public function unionAll($sql)
    {
        return $this->union($sql, 'ALL');
    }

    /**
     * 关联
     * @param  string $table
     * @return self
     */
    public function relate($table)
    {
        # code...
    }

////////////////////////////////////////// build sql helper function //////////////////////////////////////////

    public function addWhere($condition, array $param=[], $type='AND')
    {
        if ($condition) {
            $type = strtoupper(trim($type));

            if ( $this->where ) {
                $this->where .= " $type " . trim($condition);
            } else {
                $this->where = $this->where($condition);
            }

            $this->addParams($param);
        }

        return $this;
    }

    public function addOrWhere($condition, array $param=[])
    {
        return $this->addWhere($condition, $param, 'OR');
    }

    public function addAndWhere($condition, array $param=[])
    {
        return $this->addWhere($condition, $param, 'AND');
    }

    public function addSearchWhere()
    {}

    /**
     * @param array|string $param
     * @param mixed $value
     * @return $this
     */
    public function addParams($param, $value=null)
    {
        if ( $param ) {
            $param = is_array($param) ? $param : [$param=>$value];

            $this->params = $this->params ? array_merge($this->params, $param) : $param;
        }

        return $this;
    }

    /**
     * @param array $params
     * @return $this
     */
    public function setParams(array $params)
    {
        if ($params) {
            $this->params = $params;
        }

        return $this;
    }

    /**
     * @return array
     */
    public function getParams()
    {
        return $this->params;
    }

////////////////////////////////////////// build sql helper function //////////////////////////////////////////

    /**
     * @param array $elements
     * @return $this
     */
    public function setElements(array $elements)
    {
        foreach ($elements as $name => $value) {
            $this->$name = $value;
        }

        return $this;
    }

    /**
     * @return array
     */
    public function getElements()
    {
        $elements = static::enabledElements();

        foreach ($elements as $name => $value) {
            if ($this->$name === '') {
                unset($elements[$name]);
                continue;
            }

            $elements[$name] = $this->$name;
        }

        return $elements;
    }

    /**
     * @return string
     * @throws \DLogicException
     */
    public function toString()
    {
        if ( !$this->select || $this->select === '*') {
            $this->select();
        }

        if ( !$this->from ) {
            throw new \DLogicException('请通过对象属性 $from 设置要查询的表名。');
        }

        $attrs = $this->getElements();

        return implode(' ', $attrs);
    }

    /**
     * @return array
     */
    static public function enabledElements()
    {
        return [
            'select'    => '*',
            'from'      => '',
            'join'      => '',
            'where'     => '',
            'in'        => '',
            'like'      => '',
            'between'   => '',
            'order'     => '',
            'group'     => '',
            'having'    => '',
            'limit'     => '',
            'offset'    => '',
            'union'     => '',
            // 'relate'     => '',
        ];
    }

    /**
     * @return AbstractQuery
     */
    public function reset(array $except=[])
    {
        return $this->clear($except);
    }

    /**
     * @return $this
     */
    public function clear(array $except=[])
    {
        $elements = static::enabledElements();

        $elements['params'] = [];

        foreach ($elements as $ele => $value) {
            if ($except && in_array($ele, $except)) {
                continue;
            }

            $this->$ele = $value;
        }

        return $this;
    }


    /**
     * @throws \DLogicException
     */
    public function getSql()
    {
        $this->toString();
    }

    /**
     * @return string
     * @throws \DLogicException
     */
    public function __toString()
    {
        return $this->toString();
    }

    /**
     * @param $name
     * @param $value
     * @throws \DUnknownCalledException
     */
    public function __set($name, $value)
    {
        if ($name == 'leftJoin') {
            $this->join = 'LEFT JOIN '. trim($value);
        }

        if ($name == 'rightJoin') {
            $this->join = 'RIGHT JOIN '. trim($value);
        }

        parent::__set($name, $value);
    }



////////////////////////////////////////// build sql helper function //////////////////////////////////////////

    /**
     * [handleColumn 查询字段处理]
     * @param $fields
     * @param  bool $check 是否检查验证字段
     * @return string
     */
    public function handleColumn($fields, $check=true)
    {
        # 1 string
        if (is_string($fields)) {
            $fields         = str_replace('`','',trim($fields));

            # 全字段查询
            if ($fields == '*' || !$fields) {
                return $this->db->getTable($this->table)->getColumnNames(true);
            }

            # 多字段 id,name,title | a.id,b.name,c.title as newTitle
            if (strpos($fields, ',')!==false) {
                $a_fields       = explode(',', $fields);
                $a_new_fields   = array();

                foreach ($a_fields as $field)
                {
                    $a_new_fields[]     = $this->handleColumn($field, $check);
                }

                return implode(',', $a_new_fields );
            # 单字段 使用别名 id as userId | id userId | tb_user.id as userId ...
            } else if (strpos($fields, ' ')!==false) {

                list($field, $other)     = explode(' ', $fields,2);

                return $this->handleColumn($field, $check).' '.$other;

            # 仅一个单字段 id | a.id
            } else {

                # 带表 tb_user.id | a.id
                if ( strpos($fields,'.')!==false ) {
                    list($table, $field)     = explode('.', $fields);

                    return $table.'.`'.$field.'`';
                }

                # 检查验证
                if ($check)
                {
                    $this->db->verifyField($fields, $this->table);
                }

                return '`'.$fields.'`';
            }
        }

        # 2 array('id','name','title')
        if (is_array($fields)) {

            foreach ($fields as $key=>$field) {
                $fields[$key]     = $this->handleColumn($field, $check);
            }

            return implode(',', $fields );
        }

        return $fields;
        // $this->queryColumns = $this->formatData($fields);
    }

    protected function handleCondition($cond)
    {
        if (is_array($cond)) {
            $condStr = '';

            foreach ($cond as $name => $value) {

                $value = trim($value);

                if ( !in_array( $value{0} , ['=','<','>'] ) )
                {
                    $value = '=' . $this->quote($value);
                }

                $condStr = $this->quoteName($name) . $value;
            }

            return $condStr;
        } else if (is_object($cond)) {
            $cond = get_object_vars($cond);

            return $this->handleCondition($cond);
        } else if (is_scalar($cond)) {

            return trim($cond);
        }

        throw new \DInvalidException('param type error! unsupported type !');
    }

    /**
     * 处理条件成为 pdo 预准备样式
     * @todo > < >= <= 不能处理！
     * @param $condition
     * @param null $param
     * @param string $separator
     * @return bool|string
     */
    protected function handleWhere($condition, $param=null, $separator='AND')
    {
        if ( is_scalar($condition) ) {
            $condition = trim($condition);

            # -1 特殊情形,where 条件为 1 ：查询所有
            if ($condition == 1) {
                return 1;
            }

            $pos = strpos($condition, '=');

            # 0 特殊情形,仅一个字段 'id' 使用 in() 结构时：where id in(3,4,6)
            if ($pos===false && $param===null ) {
                return $this->quoteName($condition);
            }

            # 1 仅有一个条件 ，值为$param ; @example ('id',5) --> id=5
            if ( $pos===false && is_scalar($param) ) {
                $this->db->verifyField($condition, $this->table);
                $condition = "{$condition} = :c";
                $param     = array(':c'=>$param );
            } else {
                $arrCond    = explode('=', $condition);

                # 2 仅有一段条件，$param=null ;@example ("id = 5")
                if ( count( $arrCond ) == 2 && $param===null) {

                    $field      = trim( substr($condition, 0, $pos) );
                    $value      = trim( substr($condition, $pos+1) );
                    // md($condition, $field, $value);
                    $this->db->verifyField($field, $this->table);

                    $condition  = "{$field} = :c";
                    $param      = array(':c'=>$value );

                # 3 $param==null ; ("id = 5 AND name = 'hello'")
                # 4 $param==array ; ('id = :p1 AND name = :p2',array(':p1' => 5 ,'P2' => 'hello')   )
                } else {

                    foreach ($arrCond as $key => $value) {
                        $value = trim($value);

                        if ($key == 0) {
                            $arrCond[0] = $value;
                            continue;
                        }

                        if ($key%2 == 0) {
                            $arrCond[$key] = substr( $value, -strrpos($value, ' ') );
                        }

                        unset($arrCond[$key]);
                    }

                    #字段检查
                    $this->db->verifyField($arrCond, $this->table);
                }
            }

            $this->params = $param;
            $stringWhere    = $condition;

        # array('id'=>5 , 'name' => 'hello' )
        } else if ( is_array($condition) ) {
            $separator      = ' '.strtoupper( trim($separator) ).' ';
            //$whereStr       = '';
            $number         = 1;
            $wheres          = $param          = array();

            //字段检查
            $this->db->verifyField(array_keys($condition), $this->table);

            foreach($condition as $name => $value) {
                $bind               = ':c'.($number++);
                $wheres[]           = $this->quoteName($name)."=$bind";
                $param[$bind]       =  trim($value);
            }

            $this->params = $param;
            $stringWhere    = implode($separator, $wheres );

        } else if (is_object($condition)) {
            $condition = get_object_vars($condition);

            return $this->handleWhere($condition);
        } else {
            return false;
        }

        # 还原为常规的语句，不使用参数绑定
        // if ($type && !empty($this->arrParam) )
        // {
        //     $binds                   = array_keys($this->arrParam);
        //     $stringWhere             = str_replace($binds, $this->arrParam, $stringWhere);
        //     $this->arrParam          = array();
        // }

        unset($condition, $param, $separator);

        # 返回值
        return trim($stringWhere);

    }
}